IF OBJECT_ID('ssf.ddl_exists_assembly') IS NOT NULL
	DROP PROCEDURE ssf.ddl_exists_assembly
GO

CREATE PROCEDURE ssf.ddl_exists_assembly
	@assembly_name NVARCHAR(128)
	, @db_name NVARCHAR(128) = NULL
	, @exists_fl BIT = NULL OUTPUT
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @row_count BIGINT
			, @sql VARCHAR(MAX)
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @two_quotes CHAR(2) = CHAR(39) + CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[initialize variables]]></code_comment>*/
SET @db_name = ISNULL(@db_name, DB_NAME())
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameter]]></code_comment>*/
SET @exists_fl = NULL
-- ============

-- ============
/*<code_comment><![CDATA[if database exists]]></code_comment>*/
IF DB_ID(@db_name) IS NOT NULL
BEGIN
	-- ============
	/*<code_comment><![CDATA[test assembly existence]]></code_comment>*/
	SET @nsql = 'IF EXISTS ('
		+ @crlf + @tab + 'SELECT assembly_id'
		+ @crlf + @tab + 'FROM ' + QUOTENAME(@db_name) + '.sys.assemblies(NOLOCK)'
		+ @crlf + @tab + 'WHERE name = ' + QUOTENAME(@assembly_name, @quote)
		+ @crlf + ')'
		+ @crlf + @tab + 'SET @exists_fl = 1'
		+ @crlf + 'ELSE'
		+ @crlf + @tab + 'SET @exists_fl = 0'

	PRINT @nsql

	EXECUTE @sql_error = sp_executesql
		@nsql
		, N'@exists_fl BIT OUTPUT'
		, @exists_fl OUTPUT

	IF ISNULL(@sql_error, 1) <> 0
	BEGIN
		RAISERROR('Dynamic sql failed: %s.', 16, 1, @nsql)
		RETURN @sql_error
	END
	-- ============
END
ELSE
-- ============
/*<code_comment><![CDATA[otherwise, return false]]></code_comment>*/
	SET @exists_fl = 0

RETURN @@ERROR
GO
